﻿using System;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Data.Linq;
using PersonaLibrary.Data;

namespace PersonaLibrary.ViewModel.ExcelViewModel
{
    internal sealed class ImportAll : BaseExcelVM
    {
        private readonly LinqToDbDataContext _context = new LinqToDbDataContext();

        readonly Table<Job> _jobTable;
        readonly Table<Employee> _emplTable;
        readonly Table<Department> _depTable;
        readonly Table<Organization> _orgTable;
        readonly Table<ConnectionJobEmplDep> _relTable;

    	private bool _isImport;

        public ImportAll()
        {
            _jobTable = _context.Jobs;
            _emplTable = _context.Employees;
            _depTable = _context.Departments;
            _orgTable = _context.Organizations;
            _relTable = _context.ConnectionJobEmplDeps;
        }

        public void CreateOrganizations(object[,] columns, int row)
        {
            if (_orgTable.Any(x => x.Name == Convert.ToString(columns[row, 7]))) return;
            
            var org = new Organization
                          {
                              Name = Convert.ToString(columns[row, 7]),
                              Category = Convert.ToString(columns[row, 8]),
                              Ownership = Convert.ToString(columns[row, 6]),
                              Site = Convert.ToString(columns[row, 22])
                          };

            double oDate;
            if(double.TryParse(Convert.ToString(columns[row, 1]), out oDate))
				org.UpdateDate = DateTime.FromOADate(oDate);

            _orgTable.InsertOnSubmit(org);

            _context.SubmitChangesWithOutPartial();
        }

        public void LinkOrg(object[,] columns, int row)
        {
            if (columns[row, 21] == null) 
				return;

            if (!_orgTable.Any(x => x.Name == Convert.ToString(columns[row, 21]))) 
				return;

            var org = _orgTable.Single(x => x.Name == Convert.ToString(columns[row, 7]));
            var parentOrg = _orgTable.Single(x => x.Name == Convert.ToString(columns[row, 21]));

            org.ParentOrganization = parentOrg.Id;

            _context.SubmitChangesWithOutPartial();
        }

        public void CreateDepartament(object[,] columns, int row)
        {
            if (_depTable.Any(x => x.Name == Convert.ToString(columns[row, 9]) && x.OrganizationId == _orgTable.Single(y => y.Name == Convert.ToString(columns[row, 7])).Id))                
                return;

            var dep = new Department
                          {
                              Address = Convert.ToString(columns[row, 18]),
                              Email = Convert.ToString(columns[row, 17]),
                              Name = Convert.ToString(columns[row, 9]),
                              Office = Convert.ToString(columns[row, 16]),
                              Telephone = "(" + columns[row, 12] + ") " + columns[row, 13],
                              OrganizationId =
                                  _orgTable.Single(y => y.Name == Convert.ToString(columns[row, 7])).Id
                          };

            double oDate;
            if (double.TryParse(Convert.ToString(columns[row, 1]), out oDate))
                dep.UpdateDate = DateTime.FromOADate(oDate);

            _depTable.InsertOnSubmit(dep);

            _context.SubmitChangesWithOutPartial();
        }

        public void CreateEmploee(object[,] columns, int row)
        {
            if (_emplTable.Any(x => x.Name == Convert.ToString(columns[row, 3]) && x.Patronymic == Convert.ToString(columns[row, 4]) && x.Surname == Convert.ToString(columns[row, 2])))
                return;

            var em = new Employee();
            DateTime tryResult;
        	string birthday = Convert.ToString(columns[row, 5]);
			if (DateTime.TryParse(birthday, out tryResult))
				em.Birthday = tryResult;
			else if(!string.IsNullOrWhiteSpace(birthday))
				em.InexactBirthday = Convert.ToString(columns[row, 5]);

            em.Name = Convert.ToString(columns[row, 3]);
            em.Patronymic = Convert.ToString(columns[row, 4]);
            em.PersonalPhone = Convert.ToString(columns[row, 14]);
            em.Surname = Convert.ToString(columns[row, 2]);

            double oDate;
            if (double.TryParse(Convert.ToString(columns[row, 1]), out oDate))
                em.UpdateDate = DateTime.FromOADate(oDate);

            _emplTable.InsertOnSubmit(em);

            _context.SubmitChangesWithOutPartial();
        }

        public void CreateJob(object[,] columns, int row)
        {
            if (_jobTable.Any(x => x.Name.Replace(" ", "") == Convert.ToString(columns[row, 10]).Replace(" ", "")))
                return;

            var job = new Job {Name = Convert.ToString(columns[row, 10])};

            double oDate;
            if (double.TryParse(Convert.ToString(columns[row, 1]), out oDate))
                job.UpdateDate = DateTime.FromOADate(oDate);

            _jobTable.InsertOnSubmit(job);

            _context.SubmitChangesWithOutPartial();
        }

        public void CreateRel(object[,] columns, int row)
        {
            var org = _orgTable.Single(y => y.Name == Convert.ToString(columns[row, 7]));

            var dep = _depTable.Single(x => x.Name == Convert.ToString(columns[row, 9]) && x.OrganizationId == org.Id);
            var empl = _emplTable.Single(x => x.Name == Convert.ToString(columns[row, 3]) && x.Patronymic == Convert.ToString(columns[row, 4]) && x.Surname == Convert.ToString(columns[row, 2]));
            var job = _jobTable.Single(x => x.Name.Replace(" ", "") == Convert.ToString(columns[row, 10]).Replace(" ", ""));
            
            if (_relTable.Any(x => x.JobId == job.Id && x.EmployeeId == empl.Id && x.DepartmentId == dep.Id)) return;

            var rel = new ConnectionJobEmplDep
                          {
                              DepartmentId = dep.Id,
                              EmployeeId = empl.Id,
                              JobId = job.Id
                          };

            double oDate;
            if (double.TryParse(Convert.ToString(columns[row, 1]), out oDate))
                rel.UpdateDate = DateTime.FromOADate(oDate);

            _relTable.InsertOnSubmit(rel);

            _context.SubmitChangesWithOutPartial();
        }

        protected override void CommitOperation()
        {
            IsLoading = true;
            Action worker = delegate
                                {
                                    const int columns = 22;
                                    var range = OpenedWorksheet.Range[Offset, Missing.Value];
                                    range = range.Resize[OpenedWorksheet.UsedRange.Rows.Count, columns];
                                    object[,] values = range.Value2;

                                    AllCount = OpenedWorksheet.UsedRange.Rows.Count;

                                    for (var i = 1; i < OpenedWorksheet.UsedRange.Rows.Count; i++)
                                    {
                                        CurrentCount = i;
                                        if (isRowEmpty(values, i)) continue;

                                        CreateOrganizations(values, i);
                                        CreateDepartament(values, i);
                                        CreateEmploee(values, i);
                                        CreateJob(values, i);
                                        CreateRel(values, i);
                                    }
                                    for (var i = 1; i < OpenedWorksheet.UsedRange.Rows.Count; i++)
                                    {
                                        CurrentCount = i;
                                        if (isRowEmpty(values, i)) continue;

                                        LinkOrg(values, i);
                                    }
                                    IsLoading = false;

                                	Action commit = CommiteOperationCallBack;
                                	Dispatcher.Invoke(commit);
                                };

            worker.BeginInvoke(null, null);
        }

		private void CommiteOperationCallBack()
		{
			_isImport = true;
			base.CommitOperation();
		}

		protected void OnClosed()
		{
			if (!_isImport)
			{
				ExcelClose();
			}
		}

        private bool isRowEmpty(object[,] columns, int row)
        {
            bool result = true;

            for (int i = 1; i <= 22; i++)
            {
                if(columns[row, i] != null) return false;
            }
            return result;
        }
    }
}
